What if
Moris, January 29, 2018 - 7:49 am UTC
Hi Connor
You have bravely suggest an index even thought don't have all informations.
Have a question . What if the table was range partitioned on creation date. Do you maintain this index or modify order cols (also the type local or global suggestions are welcomed)
January 30, 2018 - 2:43 am UTC
"Maybe".
What if the partitioning scheme is weekly, and 99% of the data is more recent than sysdate-1. Then partitions give me nothiing (for *this* query).
What if only 1% of the data is in the last day. Then partitioning might be good, but only if the partitioning scheme is daily or perhaps weekly. Monthly or yearly might be a different story.
Without a full picture...you can never give a good recommendation.
Additional info
Praveen Kumar, January 29, 2018 - 2:52 pm UTC
Hi,
Many thanks for your response. The trx_number is distinct and for creation_date , yes, *all* the values more than sysdate-1.
When I created the indexes as suggested by you on both the tables the cost came down to 5 from 2427.
So when we look at the explain plan , do we just go ahead and create indexes on the *filter* columns only or is there anything else we need to take care of while creating the indexes.
I mean I want to know the pre requisites to keep in mind when I am creating the Indexes on the custom table by looking at the explain plan.
I would be very grateful to you if you could respond on this query of mine.
Regards
January 30, 2018 - 2:40 am UTC
I dont look at the explain plan (generally).
I look at the data, its distribution and ideally, the business usage and business requirement.
If TRX_NUMBER is *unique* (which is not the same as 'distinct' - I said the *number* of distinct values), then an index on just that column would suffice.
Try spend less time on stats and metrics, and more on the data, if that makes sense.
Elaborate
Moris, January 30, 2018 - 7:16 am UTC
If TRX_NUMBER is *unique* (which is not the same as 'distinct' - I said the *number* of distinct values), then an index on just that column would suffice.
What do you mean by that? Trx_number could be actually unique but there is no such constraint on this col. So if this col contain say a sequence hence unique but there is no constraint on it -say for some reason whatever, would you still suggest a one key index on it?
January 31, 2018 - 12:03 am UTC
Lets say the table contains 1,000,000 rows, and my query predicates are:
where trx_number = :1
and created_date > :2
and we're deciding on index options of:
(trx_number,created_date) versus (created_date)
If TRX_NUMBER contains 1 distinct value, then I'd probably not use it in the index *but* if all that I am *selecting* is trx_number and created_date, then i *might* do so, not to speed up index access, but to remove the table access.
If TRX_NUMBER contains 10 distinct values, then I *probably* wont put an index on it, but I *might* depending on clustering factor and skew, and if I did, I would probably compress the leading column
If TRX_NUMBER contains 100 distinct values, then that probability of its usefulness in an index increases but *still* the caveat on clustering factor and skew is there.
If TRX_NUMBER contains 1000 distinct values, ie, it is unique, then that probability of its usefulness in an index makes it a near certainty.