Oracle Logbook

October 4, 2007

Oracle Directories List

Filed under: Uncategorized — heliodias @ 7:13 pm

SELECT DISTINCT (SUBSTR(MEMBER,1,INSTR(MEMBER,’\’,-1))) FROM V$LOGFILE
UNION
SELECT DISTINCT (SUBSTR(NAME,1,INSTR(NAME,’\’,-1))) FROM V$CONTROLFILE
UNION
SELECT DISTINCT (SUBSTR(FILE_NAME,1,INSTR(FILE_NAME,’\’,-1))) FROM DBA_DATA_FILES
union
SELECT (SUBSTR(VALUE,1,INSTR(VALUE,’\’,-1)))  FROM V$PARAMETER WHERE NAME LIKE ‘%pfile%’
UNION
SELECT DISTINCT (SUBSTR(NAME,1,INSTR(NAME,’\’,-1))) FROM V$ARCHIVED_LOG WHERE NAME IS NOT NULL
UNION
SELECT DISTINCT DESTINATION FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL

UNION
SELECT DISTINCT (SUBSTR(FILE_NAME,1,INSTR(FILE_NAME,’\’,-1))) FROM DBA_TEMP_FILES;

Advertisements

Driving table

Filed under: Uncategorized — heliodias @ 2:03 pm

If your database is running on CBO than Oracle can join it in three ways:

1 – Nested Loop
2 – Sort Merge
3 – Hash Join

Driving table only apply to Nested Loop, 
If both tables have index on  join column, And both tables have a filter predicate,
Then Oracle will elect the table that will become smaller after filtering to be the drive table.

eg. 

 select t1.col1,t2.col2 from t1,t2 where t1.col3=t2.col3

where t1.col1=10 and t2.col2=20;

T1 have 10000 rows

T2 have 200000 rows

A lot of people made a mistake thinking that Oracle will elect the smaller one to be the driving table.

So let´s think about it for a moment, what difference between 10000*200000 and 200000*10000

That´s why it doesn´t work like this, 

In fact Oracle will check what table will become % smaller after filtering:

Eg. T1 will have 5,000 rows (50% of reduction)

But T2 will have 20,000 rows (90% of reduction)

So the question is , what is less effort?  5,000*200,000   or 10,000*20,000

Blog at WordPress.com.