Home » RDBMS Server » Server Administration » Different execution plans for same query ??? (9.2.0.7)
Different execution plans for same query ??? [message #337264] Wed, 30 July 2008 06:02 Go to next message
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 #337266 is a reply to message #337264] Wed, 30 July 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc DBA_TAB_HISTOGRAMS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OWNER                                     VARCHAR2(30 CHAR)
 TABLE_NAME                                VARCHAR2(30)
 COLUMN_NAME                               VARCHAR2(4000)
 ENDPOINT_NUMBER                           NUMBER
 ENDPOINT_VALUE                            NUMBER
 ENDPOINT_ACTUAL_VALUE                     VARCHAR2(1000)

Regards
Michel
Re: Different execution plans for same query ??? [message #337290 is a reply to message #337264] Wed, 30 July 2008 08:04 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
If your question is WHY are there different execution plans, the answer is because there are many more rows in your table with data_source = 'FN', making a full table scan more appropriate than an index scan.
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
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.
Previous Topic: PCTUSED not permited with index
Next Topic: Time Change
Goto Forum:
  


Current Time: Thu Dec 08 20:00:08 CST 2016

Total time taken to generate the page: 0.14304 seconds