Home » RDBMS Server » Performance Tuning » Full Table Scan Instead of Index Usage (Oracle 10g)
Full Table Scan Instead of Index Usage [message #334659] Thu, 17 July 2008 10:43 Go to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
Hi There,

I just run into quite some trouble with a very large select statement. The problem is caused by unnecessary full table scans (What else?).

The following question aroses:

Why is Oracle performing a full table scan instead of using the index?

Idea A:
The table is small! -> Nope it is about 250MB... .

Idea B:
The table is completly cached in memory (a sticky one)! -> Nope it is neither in the IO Cache nor is it hold in memory... .

Idea C:
More then 30% of the size of the table is about to retrieved. -> Nope. I just retrieve a single Integer column of a multi-multi-column page.

Idea D:
No Index exist for the given column. -> Nope its the primary key column and yes I did a double-re-check - one also NASA would be proud of - and the INDEX is in use and has proper statistics.

Well I ran out of Ideas...

Doing further research the problem comes down to:
SELECT ID FROM MyTable

When I process this single statement the plan reports still a full table scan. You know I would expect that the index is used (about 12MB instead of 250MB). I tried hints and stuff but with no luck!

Does anyone have a pointer for me?


Cheers,

Martin (Kersten)
Re: Full Table Scan Instead of Index Usage [message #334661 is a reply to message #334659] Thu, 17 July 2008 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Does anyone have a pointer for me?
Other than what is contained in the STICKY post at the top of this forum which you did not read?

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?

In above URL note the link to pages which tries to answer "Why is my index not used?".
Re: Full Table Scan Instead of Index Usage [message #334667 is a reply to message #334659] Thu, 17 July 2008 11:00 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
could you post an explain plan for the statement you are running.

regards

Alan
Re: Full Table Scan Instead of Index Usage [message #334680 is a reply to message #334659] Thu, 17 July 2008 11:50 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
@anacedent

I have checked all those Oracle Documentations (Performance Guide, Concepts, SQL-Developer etc.), Tom Kite's site (and sayings) and have experiences with non-oracle DB's using the index instead of a full table scan (MySql, InnoDB, PostgreSql and alike).

@general

The general problem is really this. I just have a very complex query which almost uses only information found in indexes. Oracle is still using three or four Full Table Scans loading in 4GB of Data (which is of cause slow as in slowest).

The idea is simple: If you combine all Indexes of those tables (all indexes needed) it comes down to about around 30MB. And only when delivering each and every row I would think an row access is needed.

Playing around with the selects and plans I found out that even counting all entries oracle tries a full table scan and counts the entries by hand (1.7 secs). You know all that was needed was just checking the number of entries in the primary key index or the number of stored rows.

(select count(*) from MyTable) -> Full Table Scan
(select count(id) from MyTable) -> Full Table Scan

Since this is happening all time I wonder if there is any way to prevent that.

Idea 5:
The index is a B-Tree! So it is not cost effective to traverse. -> Can't be! (does it?) The traversal of a B-Tree in Sort-Order is O(n) and shouldn't be avoided by forcing a costly full table scan.

Idea 6:
Just don't blaim me on this one, but is there a difference within Enterprise and Standard License regarding the use of Index? I mean is this one of the penalities you get from not using Oracle Enterprise Version?

Is it advised to use materialized views to just narrow the information of a (big big) table to a handy size? I mean the costs for updating this materialized view (table) is nothing compared with the cost a full table scan on the original.

Idea 7:
May Oracle behave differently with other index types?


Cheers,

Martin (Kersten)

PS: The profile isn't sufficiant I guess. The question is simply why does Oracle go for a 250MB (or for the other table 3GB) roundtrip when a 10MB Index is available storing all the necessary information?
Re: Full Table Scan Instead of Index Usage [message #334681 is a reply to message #334680] Thu, 17 July 2008 11:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Could you generate an 10053 trace and post results?
Re: Full Table Scan Instead of Index Usage [message #334693 is a reply to message #334680] Thu, 17 July 2008 13:09 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
what version database are you running?
are your stats uptodate on the tables/index?

regards

Alan
Re: Full Table Scan Instead of Index Usage [message #334717 is a reply to message #334659] Thu, 17 July 2008 14:26 Go to previous messageGo to next message
jamesbond0077
Messages: 1
Registered: July 2008
Junior Member
hi
what is the instance configuration
you can make your instance to use index
can you post your instance configuration ?
regards
Re: Full Table Scan Instead of Index Usage [message #334773 is a reply to message #334659] Thu, 17 July 2008 23:13 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
maybe index is in unusable state?
check index again, maybe it is on lots of columns...
check index_clustering_factor
Re: Full Table Scan Instead of Index Usage [message #334839 is a reply to message #334773] Fri, 18 July 2008 05:44 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
It is an Oracle 10g Single (but big) Server with a second one in stand-by. All indexes and tables are up-to-date.

Today I have undeployed the web-app causing complex queries and tried a single request. This request took ages. It was a very same request being fast as hell two days before. There was an analysation of the tables in question the day before the trouble started.

The index related questions: Indexes are valid and up to date. The indexes are around 5 to 20MB the overall table sizes are 250MB and 3GB. The indexes are all utilized but there are bigger than biggest table scans, that are not necessary.

Are there any rules that forces oracle to use table scans? I mean I read all those information about optimizer and hints but it buggs me that Oracle still perform table scans instead of using indexes when everything you want from a table is indeed the information already in index.


Cheers,

Martin (Kersten)
Re: Full Table Scan Instead of Index Usage [message #334847 is a reply to message #334839] Fri, 18 July 2008 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there any chance that you could actually answer the questions that people are asking? We're not asking them for fun - they will provide us with information that we need to diagnose the cause of the problem.

The 10053 trace is the most useful - it will say exactly why the optimizer is choosing the plan that it its.

The most likely cause of this problem is that the stats are old or plain wrong for the table and indices in question. Have a look at the values in DBA_TABLES and DBA_INDICES for this table. This will tell you how many rows Oracle thinks are in the table, when it was analyzed, and a variety of other information.

When you say 'The indexes are up to date' - what do you mean? Do you mean that you've generated statistics for the indexes lately, or do you mean something else?

Quote:
There was an analysation of the tables in question the day before the trouble started
What exact command was used to analyse the tables?

You know all that was needed was just checking the number of entries in the primary key index or the number of stored rows.
Nope - it's got to be a count of the rows. There is no guaranteed up to date count of the rows in a table.
Re: Full Table Scan Instead of Index Usage [message #335132 is a reply to message #334847] Mon, 21 July 2008 02:46 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
> What exact command was used to analyse the tables?

The default Oracle JOB.

>Nope - it's got to be a count of the rows. There is no guaranteed up to date count of the rows in a table.

I don't have the serialization isolation. So I dont care for pending transaction but I guess the oracle distribution model since then. So its ok. But at least the index would provide faster access or is the index also 'invalid' in terms of row-existence?

> The 10053 trace is the most useful - it will say exactly why the optimizer is choosing the plan that it its.

Thats right. Thats why I shivered first place. You know having kind of this:

SELECT * FROM MyTable WHERE type IN ('type1', 'type2', ....) AND details LIKE '%Something%'

Oracle currently goes with the details first -> Full Access and query slow as in slowest
But even worth:

SELECT * FROM MyTable WHERE id = 5 (-> Primary Key)
Currently oracle goes with the Full Table Access. No usage of Index or Index Access.

We are going to reanalyse the table today (I Hope). It looks like Oracle isn't aware of the indexes being cheaper after all.


Cheers,

Martin (Kersten)

[Updated on: Mon, 21 July 2008 02:48]

Report message to a moderator

Re: Full Table Scan Instead of Index Usage [message #335150 is a reply to message #334659] Mon, 21 July 2008 04:00 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
Additional information:

After a (re)analyzation of the table the primary index now works (is favored instead of full table access).

But the details LIKE '%Test%' is still preferred instead of TYPE IN ('A', 'B', 'C', ...). The LIKE needs a full table access (3GB+) of cause and the TYPE (and NAME) column resides inside an 11MB Index.

The statistics of the table sums down to:

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
TYPE 160 109 HEIGHT BALANCED
DETAILS 633 254 HEIGHT BALANCED
NAME 126 75 HEIGHT BALANCED

The resonse time for:

WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) AND DETAILS LIKE '%Test%' is > 5min

Without the like using index:
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) is < 100ms

Any Idea why the optimizer favors the slow full table access in this scenario?
Re: Full Table Scan Instead of Index Usage [message #335182 is a reply to message #335150] Mon, 21 July 2008 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
After a (re)analyzation of the table the primary index now works (is favored instead of full table access).


Thought so. Old/missing stats.

Can you post SQL and Explain Plans for the two different queries?

Is this a single table query, or is the DETAILS column on a different table?

When you say 'Response Time'. do you mean the time to return the first of the rows, or to return all the rows.
A common mistake made in this sort of situation is to run the query in something like TOAD or SQL*Developer and report the time that it takes to come back with the first set of rows - this can be a VERY different time to the total time taken to return all the data.
Eg, if you have an Order By clause in a query, Oracle will have to retrieve all the data before it can display a single row, yet without the Order by the query will return the first row much quicker and seem to run much faster - unless you take the time to return the last row into consideration.
Re: Full Table Scan Instead of Index Usage [message #335218 is a reply to message #335182] Mon, 21 July 2008 08:52 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
It wasn't old but it was new stats causing the problem. The very same day quite some additional types where added. But I cant figure (from the histogram) why oracle can be so wrong.

After doing a 100%-table-analyzation including the newly created indexes (first attempt to speed up the messed plans) everything is now even faster than before.

Thanks for your patience and your help!

> Can you post SQL and Explain Plans for the two different queries?

Since the problem is gone I can not reproduce everything. Since my test-senarios where small here is the conclusion:

SELECT * FROM MyTable WHERE ID = '5'
Before: Full Access Cost ~ 300000 (3GB)
After: Index And Access by Index ~ 27

SELECT * FROM MyTable WHERE TYPE IN (~30 values) AND NAME IN (~10 Values) AND DETAILS LIKE '%Test%'
BEFORE: FULL ACCESS, ITERATOR ~ 300000 (3GB)
AFTER1: Index + Access By Index ~ 57
AFTER2: Index + Access By Index + Iterator ~27

(After2 is After 100% Analyzation leaded to using another Index(TYPE, NAME, ID))

Also Oracle now uses the Index information without performing fullscan. We also solved the very same problem with another table being analyzed just the day after the first problems occured. (Also a relevant table).

So all my critics I throw into the ring (couldnt believe a full table access) is now no more. Seams just to be a sampling problem
while analyzing just a few rows when changing the Histogram of some columns dramatically.


> Is this a single table query, or is the DETAILS column on a different table?

It was the very same table.


One thing bugging me is just that when counting the rows of a table the optimizer uses SORT(AGGREGATE). This is just a bit costly I guess (takes about a minute to work things out using an 80MB Index). Wouldnt it be best to just count and do not aggregate and sort? Is there a way to speed this up? (Instead of writing your own counting function)

Re: Full Table Scan Instead of Index Usage [message #335225 is a reply to message #334659] Mon, 21 July 2008 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I have checked all those Oracle Documentations (Performance Guide, Concepts, SQL-Developer etc.), Tom Kite's site (and sayings
I am VERY impressed with your ability to speed read all that material in just over 1 hour elapsed time,
but I serious wonder how much useful information was actually absorbed.


You insist on posting your purported observations which no reader can confirm or deny the validity of supposed "facts".
You never post any actual SQL.
You never post know actual output from any SQL.
You do not follow Posting Guidelines.
You expect folks to debug your problem without providing ANY independently verifiable facts.

You're On Your Own (YOYO)!
Re: Full Table Scan Instead of Index Usage [message #335234 is a reply to message #335150] Mon, 21 July 2008 09:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Here is my 2 cents for what it is worth.
Quote:

WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) AND DETAILS LIKE '%Test%' is > 5min

Without the like using index:
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) is < 100ms


These two are completely different conditions, in the sense if you use a "%" in front of a literal you are telling oracle get me rows which contains something. I don't think oracle will be able to help you with a b-tree index. In order to achieve what you want with the above syntax try reading on the CTX (Intermedia Text) and see if that helps ( I am not sure so try it out if that be of any help to you). Since your index in defined in this order (Type, Details, Name) and it (optimiser) has to decide the selectivity on the column Type saying is it worth while doing a range scan only on Type since I cannot use the second position composite key (details).

Again this is a guess.

I don't want to re-iterate what others have already mentioned.

Regards

Raj

Re: Full Table Scan Instead of Index Usage [message #335244 is a reply to message #334659] Mon, 21 July 2008 10:32 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
ok,
where clause with '%something' can't use index.
you can use select with inline query
like
SELECT *
FROM (SELECT /*+ NO_UNNEST */  * from MyTable
      WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....))
WHERE DETAILS LIKE '%Test%'    


Re: Full Table Scan Instead of Index Usage [message #335247 is a reply to message #334659] Mon, 21 July 2008 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>you can use select with inline query
How does this change/impact query performance?
Re: Full Table Scan Instead of Index Usage [message #335251 is a reply to message #334659] Mon, 21 July 2008 11:02 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
>>you can use select with inline query
>How does this change/impact query performance?
how?
if in MyTable is 1 000 000 records,
and query
SELECT * from MyTable
      WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....)


returns 100 records is 0,1s,
then it is best to scan only 100 records for DETAILS LIKE '%Test%'
rather scan 1 000 000 records
don't you think so ?

[Updated on: Mon, 21 July 2008 11:02]

Report message to a moderator

Re: Full Table Scan Instead of Index Usage [message #335402 is a reply to message #334659] Tue, 22 July 2008 01:33 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
Quote:

>I have checked all those Oracle Documentations (Performance Guide, Concepts, SQL-Developer etc.), Tom Kite's site (and sayings
I am VERY impressed with your ability to speed read all that material in just over 1 hour elapsed time,
but I serious wonder how much useful information was actually absorbed.


I just did my homework before-hand. Only thing I was reading between these posts where a resource found about Histograms in action. So nothing to really wonder about.

When you hit such a problem like this one you talk to your DBA, read the DOCs and rely on your academic training including your experience with various other DB. But talking straight, I was facing this kind of problem for the first time as do my DBA so we was kinda puzzled and when you see Oracle using a 3Gigabyte FullScan instead of an obvious (as in obvious) index scan you are kind of face-down.

@Kriptas
That was the first thing the DBA was mentioned as a potential workaround solution. But this required me to change a core query builder and would not solve the issue about the Day-Before-It-Was-Extremly-Fast-And-The-Next-Day-You-Must-Wait-For-Ages-Problem. Also may queries was affected not only the queries I was responsible so I really needed the reason.

Quote:

>>you can use select with inline query
>How does this change/impact query performance?



Using an inselect does not hurt performance that much, as far as I understand the query concepts. It is just an adhoc-in-memory table storing the temporary results. Just keep it small and you are Ok Smile

In this example it only makes sence if TYPE and NAME can be accessed using an index, which we created after the problem started to show (just a reflex I guess). If no index is available this would also result in a full scan (but the full-text-search using LIKE is also quite expensive compared to two INs) which is the primary cost-driver here - Pumping 3GB into memory where Oracle try to serve many concurrent queries about a >5 TB database is just to much to keep this table in memory.

To summarize the problem and the solution:
------------------------------------------

1. Everything was fine and worked well before things became odd.

2. One day everything works slowly. Pre- and Postsales Office reports problems -> Database server is on its limits with IO-Waits consuming > 50% time.

3. Checking the long-running queries you find out its about a certain table and affects queries you run every day.

4. Investigating the problem you find out that even Primary Key Indexes are barely used (if at all).

5. Investigating further you find out that your developer image of the DB (just being refreshed monthly or weakly) is still fast.

6. Letting explain queries in doubt you find out that Oracle became stupid overnight. Doing things like favoring LIKE instead of an Index Access and Full Scan seams to be prefareable in first place.

7. Investigating even further you see a table-analyzation happend the very same night Oracle turned stupid about the tables in question.

8. Since the Optimizer (which happend to turn mad) is based on statistics, you know what this mean -> Bad statistics. There are quite a few situations you can run into bad statistics. You let Oracle analyse only a fraction of the actual table-rows to compute statistics (around 10-30%). A lot of new information was added to the table (or selectively removed). The new (or removed) information cause a Histogram change within the column (without having a Live-Histogram attached to the columns).

9. Solution: Do a reanalyzation of the whole table (including its indexes) taking 100% of the table into account (Rule of thumb anyone?).

10. Additional supporting steps: Just add some useful indexes and play with your developer-copy of the DB.
Re: Full Table Scan Instead of Index Usage [message #335420 is a reply to message #335402] Tue, 22 July 2008 02:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There's a fair bit of "My cat is grey so therefore all cats are grey" going on in this thread.

Using a larger sample size is not a solution. It is a tactic that happened to work in your case.

Rosco's Golden Rule of Histograms
Conjunctions (ANDs) and Disjunctions (ORs) stuff histograms. The more you use them, they more they screw them up.


AND type = 'A1'
Histograms are pretty useful here; Oracle can make a reasonable estimate of the number of matching rows based on the histogram on the TYPE column.

AND name = 'B1'
Histograms are pretty useful here; Oracle can make a reasonable estimate of the number of matching rows based on the histogram on the NAME column.

AND type = 'A1'
AND name = 'B1'
Histograms are LESS useful here. If EITHER of these predicates is highly selective, Oracle will prefer an index scan on that column. Fantastic! BUT, if they are INDIVIDUALLY non-selective, but the conjunction is HIGHLY SELECTIVE, then Oralce guesses.

eg. Say 20% of rows have type = 'A1' and 30% have name = 'B1'. There will be anywhere between 0% and 20% that have both. Around 20%, a full scan would be better; closer to 0%, an index scan would be better. Who knows which it will be? Oracle doesn't!

Oracle guesses because histograms are SINGLE COLUMN - they do not capture how many rows have both values.

If there is an INDEX on those two columns alone, then the statistics on the index will give an indication as to how selective any two values are on average, but not THOSE TWO VALUES specifically.

A similar thing happens when you join tables. Will the join succeed? Or will it not match and filter the row out. Histograms don't help.

As you add more predicates and more joins, the statistics get fuzzier and fuzzier until you get to a chaotic situation (you know chaos: a butterfly flaps its wings in Washington and a hurricane wipes out Florida). A small change to the SQL can result in a dramatically different execution plan.

By you own admission, this is a complex SQL. So much so you decline to post the syntax and plan here. I accept that you are changing just one line. But it is ALL THE OTHER LINES that are creating the chaotic situation; the line you change is just the butterfly's wings.


Statistics and CBO are great, but they can't handle everything. When you know something the optimizer doesn't, you have two choices:
  1. Tell the optimizer: gather statistics if they are stale or use CARDINALITY hints. But if it's not that simple...
  2. Do the optimizer's job for it. If the optimizer CANNOT POSSIBLY understand the complexity of the SQL, use hints to dictate a preferred execution path, or Plan Stability (stored outlines).


Looks to me you are at option 2. You used to have an efficient plan; that was good luck, not good management. It's not a new problem; it's one you've always had but didn't know about.

Ross Leishman
Re: Full Table Scan Instead of Index Usage [message #335432 is a reply to message #335420] Tue, 22 July 2008 03:11 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
In some respect you are correct. The histogram of the columns in question just look like about 1% has a certain type and so the name column has. This was also only a fraction of the actual select. The real select features four joins and everything so seeing the trace of this one (after waiting an hour featuring full-table-scans all over) you believe the DBMS must went nuts to choose such a plan (a squirrel on a nut chase would have chosen a better plan by accident).

Quote:
2. You used to have an efficient plan; that was good luck, not good management.


Well doing such a trace afford to get more then 40 search options and about 12 possible tables to dynamically join (including self-joins) was quite some work. It was optimized to lead to good plans using the developer DB and tested and run on live - normal stuff of cause but worth to mention. After some weeks oracle simply went nuts. So the better plan was not by accident but by logic Smile. My tuple-formulas filled quite some paper and I tested whether the use of additional indexes are of help or not carefully. (Just to prevent misinterpretation)

This is why I prevented any move to add a live-histogram to any column first-place or the adding of additional indexes and query manipulation. I simply wanted the reason why a query running lightning fast turns out to become a turtle in its better ages... .


Cheers,

Martin (Kersten)
Re: Full Table Scan Instead of Index Usage [message #335564 is a reply to message #335432] Tue, 22 July 2008 12:59 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
there are few things when CBO chooses to use FTS rather RS,
1)index_cost_adj (I think I wrote right, maybe wrong)
this parameter adjust cost of using index, read about it.
2) index clustering_factor if it is big clustering the CBO chooses do not use index
3) there is something skatared_reads and sequentional_read parameter. if you need to get lots of records, going by the index has larger cost.

but i cant tell nothing about query:
select count(id) from mytable
if there is noncomposite index on ID CBO chooses always to use index FFS to count....
Re: Full Table Scan Instead of Index Usage [message #335587 is a reply to message #335564] Tue, 22 July 2008 17:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
All I have to add to this is that if you'd ever actually bothered to answer any of the questions you were asked, then you might have got more help.

As @Anacedent says

YOYO
Re: Full Table Scan Instead of Index Usage [message #335762 is a reply to message #335587] Wed, 23 July 2008 11:27 Go to previous messageGo to next message
MartinMM
Messages: 10
Registered: July 2008
Junior Member
Well I wasn't that kind of YOYO Smile But you are right. The problem was that I was looking for a general problem only finding hints by doing sample queries.

When I actually saw Oracle preferring a FTS of a LIKE '%...' over an index scan for a simple list of strings then I know I was right about the hot-shot guess.

So I give kudos to everyone posting tips and hints. I learned additional stuff for sure but I knew that there is a lot more to learn about Oracle. I think I will need additional certification course(s) to become more knowledge-complete. So I will do so in the nearer future.


Cheers and many thanks,

Martin (Kersten)
Re: Full Table Scan Instead of Index Usage [message #335773 is a reply to message #335762] Wed, 23 July 2008 12:52 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
After long time I am back.... last post which i did was in Jan 2005 ....

As this problem is resolved... lets have something more to it....

[Edit MC: Remove copyrighted content of Metalink note 77496.1, don't do that]

NOTE: The bind variable may contain trailing '%' or no '%' at all. Regardless, an index will not be used.


If cost-based optimization is used, Oracle will use full table scans for all of the above cases shown for rule-based optimzation. Additionally, the cost-based optimizer may decide to use full table scans if the table has not been analyzed, if the table is small, if the indexed columns are not selective, or if the optimization goal is set to ALL_ROWS.

To avoid unplanned full table scans, make sure the query can use an index.


Enjoy..Wink

[Updated on: Wed, 23 July 2008 13:21] by Moderator

Report message to a moderator

Re: Full Table Scan Instead of Index Usage [message #335778 is a reply to message #335773] Wed, 23 July 2008 13:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Welcome back.
You could have said that in a gorgeous one-liner.
"Refer metalink Note:77496.1"
which was last updated in March 2002 and ***kind of*** outdated.
Re: Full Table Scan Instead of Index Usage [message #335879 is a reply to message #335778] Thu, 24 July 2008 01:26 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Most of people dont have access to metalink

Earlier even i don't have that access... it all depend on company/organization you work .... Smile

I think i would have changed that in my wording ...Very Happy

For the people who don't have access to metalink... its summary of Metalink note 77496.1

[Removed copyrighted Material. Posting metalink material is ILLEGAL.
Mahesh Rajendran]

[Updated on: Thu, 24 July 2008 03:07] by Moderator

Report message to a moderator

Re: Full Table Scan Instead of Index Usage [message #335890 is a reply to message #335879] Thu, 24 July 2008 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Access to Metalink is reserved to those that pay for support.
Material inside Metalink is protected by copyright law.
You are not allowed to post them.

Regards
Michel
Re: Full Table Scan Instead of Index Usage [message #335929 is a reply to message #335879] Thu, 24 July 2008 03:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@sunil_v_mishra
Just checked your blog.
http://oracle-by-dba.blogspot.com/2008/07/index-not-used-by-table.html
This article along with many others are just copy-paste from
metalink notes.
Ironically, you have a copyright notice for pirated contents @!&^*%!!.



./fa/4685/0/
  • Attachment: copyright.GIF
    (Size: 6.22KB, Downloaded 4420 times)
Re: Full Table Scan Instead of Index Usage [message #337540 is a reply to message #335929] Thu, 31 July 2008 03:23 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
On this blog

http://oracle-by-dba.blogspot.com/2008/07/index-not-used-by-table.html

I said page is by me not content Wink

The blog is for free support ... not getting money for that Wink on contrary its doing publicity of oracle product.

Do you think any one is writting any thing new ...?
Answer is no.

Every thing is already there, no one is doing new, just reading doc of oracle & writting what they think.

As per copy right if i change grammer and write same content than its not violating any copy right act ... you can say its your own ....

As per me i don't have any issue with that content on that blog

Many thing u can get in many book regarding oracle DBA which are also present at metalink doc.

I know somthing and i am pasting it on forum Very Happy, I am thrid-party for ora-faq, oracle can't do anything if i paste any metalink content by copying it from there.

I know law...

If some one copy righted "I LOVE YOU" then i cannot copy and paste on any forum.... I LOVE YOU.


Re: Full Table Scan Instead of Index Usage [message #337554 is a reply to message #337540] Thu, 31 July 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
oracle can't do anything if i paste any metalink content by copying it from there.

Against you, no. Against OraFAQ and its owner/administrator, yes.
Do you want OraFAQ been closed?
Do you want Frank has to pay fine or go to jail?
Good guy!

Quote:
The blog is for free support ... not getting money for that

No but you try to get recognition, this is another money.

Quote:
If some one copy righted "I LOVE YOU" then i cannot copy and paste on any forum.... I LOVE YOU.

Except that no one can copyright this but Metalink materials yes.

More I read your post, more I think pretty good things about you (you can use this freely, I will not copyright it).

Regards
Michel

[Updated on: Thu, 31 July 2008 04:05]

Report message to a moderator

Re: Full Table Scan Instead of Index Usage [message #337571 is a reply to message #337554] Thu, 31 July 2008 05:06 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Michel thank...

Just want to clarify ....some thing Cool

I still have one private message in my orafaq which i got long time back (in Feb 2006).... subject was Msg. from ORACLE Recruitment Team

Hi Sunil,

Greetings from ORACLE Recruitment Team. We have several positions at our Hyderabad IDC and looking for skilled professionals in Oracle Technologies.

If you are interested in exploring an oppurtunity with ORACLE, forward your resume to {.........@oracle.com}

Regards,
.......[delete by sunil]
Recruitment Team | Oracle India |Hyderabad|

Now for what I need recognition... Very Happy

I already got recognised by many other things, & now currently I am with good organization.... where i can explore many things and other technologies Wink

Knowledge have no ending..... & we learn till our last breath

Note:-[1] Long time back i was recognised through orafaq... Smile
[2] In Year 2006 month feb, I dont had access to metalink, right now i do have but because of my current company (oragnization where i work)
[3] I dont want FRANK sir to go jail because of me, I am DBA by reading content of sir only.

[Updated on: Thu, 31 July 2008 05:13]

Report message to a moderator

Re: Full Table Scan Instead of Index Usage [message #449555 is a reply to message #334659] Tue, 30 March 2010 07:29 Go to previous messageGo to next message
elizas
Messages: 2
Registered: March 2010
Location: india
Junior Member
Sometimes there is a need to get the record count of every table in a SQL Server database. The common method used for achieving this is doing a select count(*) from table_name but this can create a lot of overhead in large tables present in large databases.

The record count of every user table is maintained in the sysindexes system table . There is a indid column in the sysindexes table and every user table will have an entry in sysindexes with indid value of either 0 or 1 , but not both. The rowcnt column corresponding to indid 0 or 1 gives us the total row count of a table.

The User Defined Function given below shows how to retrieve row count for a table by making use of sysindexes.

Any suggestions are welcomed.
Re: Full Table Scan Instead of Index Usage [message #449557 is a reply to message #334659] Tue, 30 March 2010 07:39 Go to previous message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is this oracle question?
It appears to be sqlserver you are asking about and this is an oracle forum.
Previous Topic: 11g- query Performance issue
Next Topic: Missing Index but strange column
Goto Forum:
  


Current Time: Wed Dec 07 06:54:38 CST 2016

Total time taken to generate the page: 0.13779 seconds