Home » RDBMS Server » Performance Tuning » need assistance to tune the query (solarin 5.10, oracle 10.2.0.3)
need assistance to tune the query [message #336066] Thu, 24 July 2008 11:56 Go to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
Experts,

i need assistance to tune the following query. i am seeing full table scan on one table which has 40 million records. is there any way to avoid the full table scan.

please find the query and execution plan.

SELECT pc_name, 
sid, 
pgid, 
bgid, 
cgid, 
resetgroups, 
customer_type, 
license_key.lid, 
license_key.hwid, 
license_key.pseudo_id, 
license_key.type, 
license_key.id, 
license_key.platformid, 
manufacturer, 
map_type, 
status_code, 
license_key.pc_partno, 
license_key.mfg_partno, 
license_key.short_description, 
license_key.long_description, 
license_key.vendor_data, 
license_key.carrierid, 
license_key.adsid, 
license_key.item_list_name, 
license_key.price_version, 
initial_download_eventid, 
last_download_eventid, 
last_delete_eventid, 
last_ta_eventid, 
to_char(last_delete_date), 
to_char(last_download_date), 
last_upgrade_eventid, 
last_transfer_eventid, 
last_deactivate_eventid, 
bill_once, 
method, 
basis, 
value, 
eventid, 
to_char(startdate, 'MM/DD/YYYY'), 
to_char(enddate, 'MM/DD/YYYY'), 
price, 
dap, 
price_currency, 
dap_currency, 
prepay_flag, 
ext_count, 
to_char(billeddate, 'dd-mon-yyyy hh24:mi:ss'), 
to_char((sysdate + ( local_time - gmt )), 'dd-mon-yyyy hh24:mi:ss'), 
to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'), 
price_type, 
node_id, 
node_name 
FROM 
cdsvzw.license license, 
cdsvzw.license_key license_key, 
cdsvzw.license_control license_control, 
cdsvzw.subscription subscription, 
cdsvzw.sidmap sidmap 
where sidmap.pc_name = :1 
and sidmap.pseudo_id = license_key.pseudo_id 
and sidmap.code >= 0 and license.method = 3 
and license_key.status_code >= 0 
and license_key.type = :2 
and license.lid = license_key.lid 
and license.lid = license_control.lid 
and license.lid = subscription.lid 
and nvl(license_control.bill_once, 'N') = 'N' 
and to_char(subscription.billeddate, 'YYYYM M') < to_char(sysdate, 'YYYYMM') 
and to_char(subscription.startdate, 'YYYYMM') < to_char(sysdate, 'YYYYMM') 
and trunc(nvl(subscription.enddate, sysdate + 2)) > trunc(sysdate) 
and nvl(subscription.eventid, -1) > 0 
and to_number(to_char(subscription.startdate, 'DD')) = 24 
and mod(subscription.lid, :3) = :4 
and prepay_flag = :5

[B]execution plan [/B]


-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                    |     1 |   562 |   189K  (3)| 00:37:53 |       |       |
|   1 |  NESTED LOOPS                          |                    |     1 |   562 |   189K  (3)| 00:37:53 |       |       |
|   2 |   NESTED LOOPS                         |                    |     1 |   456 |   189K  (3)| 00:37:53 |       |       |
|   3 |    NESTED LOOPS                        |                    |     1 |   248 |   189K  (3)| 00:37:53 |       |       |
|   4 |     NESTED LOOPS                       |                    |     1 |   183 |   189K  (3)| 00:37:53 |       |       |
|   5 |      PARTITION HASH ALL                |                    |     1 |   163 |   189K  (3)| 00:37:53 |     1 |    32 |
|*  6 |       TABLE ACCESS FULL                | SUBSCRIPTION       |     1 |   163 |   189K  (3)| 00:37:53 |     1 |    32 |
|*  7 |      TABLE ACCESS BY GLOBAL INDEX ROWID| LICENSE            |     1 |    20 |     1   (0)| 00:00:01 | ROWID | ROWID |
|*  8 |       INDEX UNIQUE SCAN                | LICENSE_PK         |     1 |       |     1   (0)| 00:00:01 |       |       |
|*  9 |     TABLE ACCESS BY GLOBAL INDEX ROWID | LICENSE_CONTROL    |     1 |    65 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 10 |      INDEX UNIQUE SCAN                 | LICENSE_CONTROL_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY GLOBAL INDEX ROWID  | LICENSE_KEY        |     1 |   208 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 12 |     INDEX UNIQUE SCAN                  | LICENSE_KEY_PK     |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 13 |   TABLE ACCESS BY GLOBAL INDEX ROWID   | SIDMAP             |     1 |   106 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 14 |    INDEX UNIQUE SCAN                   | SIDMAP_PK          |     1 |       |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------



Regards,

Jyothy...

[Updated on: Thu, 24 July 2008 11:57]

Report message to a moderator

Re: need assistance to tune the query [message #336068 is a reply to message #336066] Thu, 24 July 2008 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
is there any way to avoid the full table scan.

Keep your statistics up to date, Oracle thinks there is 1 row in your table not 40 million.

Regards
Michel

Report message to a moderator

Re: need assistance to tune the query [message #336070 is a reply to message #336068] Thu, 24 July 2008 12:44 Go to previous messageGo to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
statistics are up to date. 10 days ago we gathered statistics.

Report message to a moderator

Re: need assistance to tune the query [message #336072 is a reply to message #336066] Thu, 24 July 2008 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
These functions preclude index use
and to_char(subscription.billeddate, 'YYYYM M') < to_char(sysdate, 'YYYYMM') 
and to_char(subscription.startdate, 'YYYYMM') < to_char(sysdate, 'YYYYMM') 
and trunc(nvl(subscription.enddate, sysdate + 2)) > trunc(sysdate) 
and nvl(subscription.eventid, -1) > 0 
and to_number(to_char(subscription.startdate, 'DD')) = 24 
and mod(subscription.lid, :3) = :4

IMO, without a re-design of table, a FTS of subscription table is required.

Report message to a moderator

Re: need assistance to tune the query [message #336073 is a reply to message #336070] Thu, 24 July 2008 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe if you posted the FULL plan result we can know what happens in step 6.

Regards
Michel

Report message to a moderator

Re: need assistance to tune the query [message #336076 is a reply to message #336073] Thu, 24 July 2008 13:07 Go to previous messageGo to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
please find the full plan.

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(NVL("SUBSCRIPTION"."EVENTID",(-1))>0 AND MOD("SUBSCRIPTION"."LID",TO_NUMBER(:3))=TO_NUMBER(:4) AND
              TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("SUBSCRIPTION"."STARTDATE"),'DD'))=24 AND
              TO_CHAR(INTERNAL_FUNCTION("SUBSCRIPTION"."STARTDATE"),'YYYYMM')<TO_CHAR(SYSDATE@!,'YYYYMM') AND
              TO_CHAR(INTERNAL_FUNCTION("SUBSCRIPTION"."BILLEDDATE"),'YYYYM M')<TO_CHAR(SYSDATE@!,'YYYYMM') AND
              TRUNC(NVL("SUBSCRIPTION"."ENDDATE",SYSDATE@!+2))>TRUNC(SYSDATE@!) AND "PREPAY_FLAG"=:5)
   7 - filter(TO_NUMBER("LICENSE"."METHOD")=3)
   8 - access("LICENSE"."LID"="SUBSCRIPTION"."LID")
   9 - filter(NVL("LICENSE_CONTROL"."BILL_ONCE",'N')='N')
  10 - access("LICENSE"."LID"="LICENSE_CONTROL"."LID")
  11 - filter("LICENSE_KEY"."STATUS_CODE">=0 AND "LICENSE_KEY"."TYPE"=TO_NUMBER(:2))
  12 - access("LICENSE"."LID"="LICENSE_KEY"."LID")
  13 - filter("SIDMAP"."CODE">=0 AND "SIDMAP"."PC_NAME"=:1)
  14 - access("SIDMAP"."PSEUDO_ID"="LICENSE_KEY"."PSEUDO_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
       "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
       "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
       "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
       "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."METHOD"[VARCHAR2,30], "BASIS"[VARCHAR2,3],
       "VALUE"[VARCHAR2,720], "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_UPGRADE_EVENTID"[NUMBER,22],
       "LAST_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_DELETE_EVENTID"[NUMBER,22], "LAST_TA_EVENTID"[NUMBER,22],
       "LAST_TRANSFER_EVENTID"[NUMBER,22], "LAST_DEACTIVATE_EVENTID"[NUMBER,22], "LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,
3],

       "LAST_DELETE_DATE"[TIMESTAMP,11], "LAST_DOWNLOAD_DATE"[TIMESTAMP,11], "LICENSE_KEY"."LID"[NUMBER,22],
       "LICENSE_KEY"."HWID"[VARCHAR2,720], "LICENSE_KEY"."PSEUDO_ID"[NUMBER,22], "LICENSE_KEY"."ID"[VARCHAR2,720],
       "MANUFACTURER"[VARCHAR2,720], "MAP_TYPE"[VARCHAR2,30], "LICENSE_KEY"."STATUS_CODE"[NUMBER,22],
       "LICENSE_KEY"."TYPE"[NUMBER,22], "LICENSE_KEY"."PLATFORMID"[NUMBER,22], "LICENSE_KEY"."PC_PARTNO"[VARCHAR2,720],
       "LICENSE_KEY"."MFG_PARTNO"[VARCHAR2,720], "LICENSE_KEY"."SHORT_DESCRIPTION"[VARCHAR2,720],
       "LICENSE_KEY"."LONG_DESCRIPTION"[VARCHAR2,4000], "LICENSE_KEY"."VENDOR_DATA"[VARCHAR2,4000],
       "LICENSE_KEY"."ITEM_LIST_NAME"[NVARCHAR2,4000], "LICENSE_KEY"."CARRIERID"[NUMBER,22],
       "LICENSE_KEY"."ADSID"[NUMBER,22], "LICENSE_KEY"."PRICE_VERSION"[NUMBER,22], "SIDMAP"."PC_NAME"[VARCHAR2,90],
       "SID"[VARCHAR2,720], "PGID"[NVARCHAR2,480], "BGID"[NVARCHAR2,480], "CGID"[NVARCHAR2,480], "RESETGROUPS"[VARCHAR2,
3],

       "CUSTOMER_TYPE"[VARCHAR2,30]
   2 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
       "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
       "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
       "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
       "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."METHOD"[VARCHAR2,30], "BASIS"[VARCHAR2,3],
       "VALUE"[VARCHAR2,720], "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_UPGRADE_EVENTID"[NUMBER,22],
       "LAST_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_DELETE_EVENTID"[NUMBER,22], "LAST_TA_EVENTID"[NUMBER,22],
       "LAST_TRANSFER_EVENTID"[NUMBER,22], "LAST_DEACTIVATE_EVENTID"[NUMBER,22], "LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,
3],

       "LAST_DELETE_DATE"[TIMESTAMP,11], "LAST_DOWNLOAD_DATE"[TIMESTAMP,11], "LICENSE_KEY"."LID"[NUMBER,22],
       "LICENSE_KEY"."HWID"[VARCHAR2,720], "LICENSE_KEY"."PSEUDO_ID"[NUMBER,22], "LICENSE_KEY"."ID"[VARCHAR2,720],
       "MANUFACTURER"[VARCHAR2,720], "MAP_TYPE"[VARCHAR2,30], "LICENSE_KEY"."STATUS_CODE"[NUMBER,22],
       "LICENSE_KEY"."TYPE"[NUMBER,22], "LICENSE_KEY"."PLATFORMID"[NUMBER,22], "LICENSE_KEY"."PC_PARTNO"[VARCHAR2,720],
       "LICENSE_KEY"."MFG_PARTNO"[VARCHAR2,720], "LICENSE_KEY"."SHORT_DESCRIPTION"[VARCHAR2,720],
       "LICENSE_KEY"."LONG_DESCRIPTION"[VARCHAR2,4000], "LICENSE_KEY"."VENDOR_DATA"[VARCHAR2,4000],
       "LICENSE_KEY"."ITEM_LIST_NAME"[NVARCHAR2,4000], "LICENSE_KEY"."CARRIERID"[NUMBER,22],
       "LICENSE_KEY"."ADSID"[NUMBER,22], "LICENSE_KEY"."PRICE_VERSION"[NUMBER,22]
   3 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
       "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
       "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
       "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
       "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."LID"[NUMBER,22], "LICENSE"."METHOD"[VARCHAR2,30],
       "BASIS"[VARCHAR2,3], "VALUE"[VARCHAR2,720], "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22],
       "LAST_UPGRADE_EVENTID"[NUMBER,22], "LAST_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_DELETE_EVENTID"[NUMBER,22],
       "LAST_TA_EVENTID"[NUMBER,22], "LAST_TRANSFER_EVENTID"[NUMBER,22], "LAST_DEACTIVATE_EVENTID"[NUMBER,22],
       "LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,3], "LAST_DELETE_DATE"[TIMESTAMP,11], "LAST_DOWNLOAD_DATE"[TIMESTAMP,11]
   4 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
       "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
       "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
       "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
       "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."LID"[NUMBER,22], "LICENSE"."METHOD"[VARCHAR2,30],
       "BASIS"[VARCHAR2,3], "VALUE"[VARCHAR2,720]
   5 - "SUBSCRIPTION"."LID"[NUMBER,22], "SUBSCRIPTION"."EVENTID"[NUMBER,22],
       "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11], "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11],
       "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22], "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9],
       "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3], "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11],
       "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22], "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000]
   6 - "SUBSCRIPTION"."LID"[NUMBER,22], "SUBSCRIPTION"."EVENTID"[NUMBER,22],
       "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11], "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11],
       "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22], "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9],
       "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3], "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11],
       "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22], "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000]
   7 - "LICENSE"."LID"[NUMBER,22], "LICENSE"."METHOD"[VARCHAR2,30], "BASIS"[VARCHAR2,3], "VALUE"[VARCHAR2,720]
   8 - "LICENSE".ROWID[ROWID,10], "LICENSE"."LID"[NUMBER,22]
   9 - "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_UPGRADE_EVENTID"[NUMBER,22], "LAST_DOWNLOAD_EVENTID"[NUMBER,22],
       "LAST_DELETE_EVENTID"[NUMBER,22], "LAST_TA_EVENTID"[NUMBER,22], "LAST_TRANSFER_EVENTID"[NUMBER,22],
       "LAST_DEACTIVATE_EVENTID"[NUMBER,22], "LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,3], "LAST_DELETE_DATE"[TIMESTAMP,11]
,

       "LAST_DOWNLOAD_DATE"[TIMESTAMP,11]
  10 - "LICENSE_CONTROL".ROWID[ROWID,10]
  11 - "LICENSE_KEY"."LID"[NUMBER,22], "LICENSE_KEY"."HWID"[VARCHAR2,720], "LICENSE_KEY"."PSEUDO_ID"[NUMBER,22],
       "LICENSE_KEY"."ID"[VARCHAR2,720], "MANUFACTURER"[VARCHAR2,720], "MAP_TYPE"[VARCHAR2,30],
       "LICENSE_KEY"."STATUS_CODE"[NUMBER,22], "LICENSE_KEY"."TYPE"[NUMBER,22], "LICENSE_KEY"."PLATFORMID"[NUMBER,22],
       "LICENSE_KEY"."PC_PARTNO"[VARCHAR2,720], "LICENSE_KEY"."MFG_PARTNO"[VARCHAR2,720],
       "LICENSE_KEY"."SHORT_DESCRIPTION"[VARCHAR2,720], "LICENSE_KEY"."LONG_DESCRIPTION"[VARCHAR2,4000],
       "LICENSE_KEY"."VENDOR_DATA"[VARCHAR2,4000], "LICENSE_KEY"."ITEM_LIST_NAME"[NVARCHAR2,4000],
       "LICENSE_KEY"."CARRIERID"[NUMBER,22], "LICENSE_KEY"."ADSID"[NUMBER,22], "LICENSE_KEY"."PRICE_VERSION"[NUMBER,22]
  12 - "LICENSE_KEY".ROWID[ROWID,10], "LICENSE_KEY"."LID"[NUMBER,22]
  13 - "SIDMAP"."PC_NAME"[VARCHAR2,90], "SID"[VARCHAR2,720], "PGID"[NVARCHAR2,480], "BGID"[NVARCHAR2,480],
       "CGID"[NVARCHAR2,480], "RESETGROUPS"[VARCHAR2,3], "CUSTOMER_TYPE"[VARCHAR2,30]
  14 - "SIDMAP".ROWID[ROWID,10]

Report message to a moderator

Re: need assistance to tune the query [message #336080 is a reply to message #336076] Thu, 24 July 2008 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As you can see filter in step 6 can't be indexed so, as Ana said, FTS is avoidable.
By the way, you have an error in one of the conditions.
In addition, some conditions are suboptimal, don't use TO_CHAR on dates to compare them.

Regards
Michel

Report message to a moderator

Re: need assistance to tune the query [message #336094 is a reply to message #336066] Thu, 24 July 2008 14:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It appears to me that 5 different columns of subscription tables are part of the WHERE clause.
Even if you can change the SQL to utilize indexes on 4 out of the 5 columns, the 5th & remaining column would still require a FTS.
So unless & until you can get the optimizer to use 5 indexes against table subscription, a FTS will be required.

>and mod(subscription.lid, :3) = :4
The condition above might be a very clever implementation trick but I suspect will be the most difficult to get used by/with an index.
With the current design/implementation, a FTS is a fact of life.

Report message to a moderator

Re: need assistance to tune the query [message #336120 is a reply to message #336094] Thu, 24 July 2008 22:22 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
anacedent wrote on Fri, 25 July 2008 05:48
So unless & until you can get the optimizer to use 5 indexes against table subscription, a FTS will be required.

I think I understand what this means, but it is terribly misleading.

Unless you can get the optimiser to scan indexes on all of the predicates, then redundant table reads are unavoidable. Those reads will not be a FTS, they will be TABLE ACCESS BY INDEX ROWID (which is often worse!).

But this may not be a bad thing. If scanning on just ONE of those predicates is highly selective, then including others in the index(es) may not add much value.


This one looks non-selective. I guess most rows will predate SYSDATE. I would not bother changing it.
and to_char(subscription.billeddate, 'YYYYM M') < to_char(sysdate, 'YYYYMM')


Same here
and to_char(subscription.startdate, 'YYYYMM') < to_char(sysdate, 'YYYYMM')


There may be some value in indexing this one
and trunc(nvl(subscription.enddate, sysdate + 2)) > trunc(sysdate)
This is functionally equivalent to "endate after today - no NULLs". Try rewriting it as
and subscription.enddate >= trunc(sysdate) + 1
and index subscription.enddate.

Rewrite the following
and nvl(subscription.eventid, -1) > 0
to 
subscription.eventid > 0
but it doesn't sound very selective to me, so I wouldn't index it.

This is a strange one
and to_number(to_char(subscription.startdate, 'DD')) = 24
Why would you only be interested in subscriptions starting on the 24th of ANY month? If this is legitimate, you could look into a function-based index.

and mod(subscription.lid, :3) = :4
Don't know how selective this would be, but you cannot index it because of the variable modulus in the MOD().


In summary, you could try indexing:
subscription( to_number(to_char(subscription.startdate, 'DD')), enddate)

Ross Leishman

Report message to a moderator

Previous Topic: Testing of generated test data from Sequence
Next Topic: need help to optimize the query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 14:52:05 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.