Different execution plans for same query ??? [message #337264] |
Wed, 30 July 2008 06:02 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
nazbrian
Messages: 36 Registered: July 2008
|
Member |
|
|
Hi,
Following is the output.
SQL> explain plan for
2 select * from fem_term_deposits where as_of_date = to_date('16-Jul-2008','DD-Mon-YY') and data_source = 'CB' ;
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 579 | 2 | | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID| FEM_TERM_DEPOSITS | 1 | 579 | 2 | 21 | 21 |
| 2 | INDEX RANGE SCAN | FEM_TD_AOD | 1 | | 1 | 21 | 21 |
--------------------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
10 rows selected.
SQL> explain plan for
2 select * from fem_term_deposits where as_of_date = to_date('16-Jul-2008','DD-Mon-YY') and data_source = 'FN' ;
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4859K| 2683M| 53037 | | |
| 1 | TABLE ACCESS FULL | FEM_TERM_DEPOSITS | 4859K| 2683M| 53037 | 21 | 21 |
-------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
9 rows selected.
SQL>
The table is analyzed!
In the above 2 query's I have just one column value is different, My doubt is how oracle will know FTS/Indexes Scan?
I mean, Which table the statisics are stored for column values ?
Brian
|
|
|
|
|
Re: Different execution plans for same query ??? [message #337484 is a reply to message #337264] |
Thu, 31 July 2008 01:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
nazbrian
Messages: 36 Registered: July 2008
|
Member |
|
|
Hi Michel,
I have following output from DBA_TAB_HISTOGRAMS.
SQL> l
1 select * from DBA_TAB_HISTOGRAMS
2 where table_name = 'FEM_TERM_DEPOSITS' and column_name in ('AS_OF_DATE','DATA_SOURCE')
3* order by column_name
SQL> /
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ -------------------- --------------- -------------- --------------------
BOIDW FEM_TERM_DEPOSITS AS_OF_DATE 0 2453430
BOIDW FEM_TERM_DEPOSITS AS_OF_DATE 1 2454191
BOIDW FEM_TERM_DEPOSITS DATA_SOURCE 0 3.4922E+35
BOIDW FEM_TERM_DEPOSITS DATA_SOURCE 1 3.6504E+35
SQL>
Based on the above output, I'm not clear why there is different execution plans based on values in the column "DATA_SOURCE".
I understand that Oracle makes its own judgement to use index scan or FTS. But as a research I want to how know oracle make s the execution Plan.
Brian.
|
|
|