if that is what you were trying to point out, you picked an opposite example, an ANTI-example.

Something has changed - the data is larger. Something else probably changed too - the volume of queries has changed, the volume of users has changed (someone created all of that data) so the amount of CPU, Memory and network available to the query has in fact changed (more things using them, less for you to use)


there are two types of queries in the world.


Ones that take about the same amount of time to retrieve data from a table(s) regardless of the size of the table. These generally need to process and return the same number of rows regardless of the table size.

Examples:

a) you gave us a classic one above. Find the last transaction (1 record) for a given customer. That'll be an index min/max scan. It will perform N IO's where N= height of the index. The height of a index is generally very small (1,2,3,4,5) even for millions or billions of rows.

b) give me the last 10 visits for a patient

c) look up this primary key


Others tend to take longer as the volume of data grows, examples:


a) what is the sum of sales over time (no predicate, sum all rows)

b) how many page views have I done (a predicate, look at my records, but over time I have to count more and more records)

c) please find all of my records in this unindexed table (full scan, takes longer and longer)


Each of those has solutions - I might use a materialized view for (a) and (b). I might add an index for (c)



Indexing by the way might be good for up to 0.5% (zero point five) in some cases, up to 100% in others. It depends.

Suppose you have a table that gets 100 rows per block. Suppose you want to retrieve 1% of the rows. Do you want to use an index?

If you say "yes, obviously", then I will tell you that the 1% of the rows you want are each on a different block, there are no blocks such that 2 rows on there are both wanted by you - each block has 1 row. Now..... do you want to use an index? NOPE - it would be really bad to read every block a single block at a time via an index. Full scan would rock and roll.

If you say "no", then I will tell you that this table has 100 blocks, and the first block has 100 rows with a value of 1, the second block has a 100 rows with 2 and so on. The query "where id = 1" should of course use an index to find the single block....


You see - it depends, there isn't a single answer. You have to actually understand how the data arrives and understand the nature of the question you are trying to answer (the queries). Every problem might be slightly different from every other problem. There is no silver bullet.

What you can (and must) do is... Understand how the database works. Understand data patterns (1% - not as simple as it seems). Understand what data structures you have available to you (hash cluster, btree cluster, index organized tables, partitioning, the ability to reorg a table with order by using dbms_redefinition, covering indexes, function based indexes, materialized views, etc etc etc) and then after understanding the problem you are facing - apply the right tool(s) in the right fashion.

THe answer is "it depends"

If it takes 3 seconds hours to find those 1,000,000 rows, then the 10 seconds to sort them is a large percentage of the execution time.

But if it takes 10 hours to find those 1,000,000 rows, then the 10 seconds to sort them is irrelevant.


The same holds true for *any* number of records - you understand the data, understand how it needs to be accessed, and design/implement the physical design to meet those goals.

Hope this helps.

1) the sorting cost will be proportional to the number of rows (and their size) to be sorted. If you only get 5 rows (even from a 10000G table), it will be quick to sort them

2) if a table is growing *steadily* then why bother *collecting* statistics. Just set them manually. eg if you add 100,000 rows per day, just bump up the row counts and block counts accordingly each day (or even more frequently if you need to). SImilarly, you know the high water mark on (say) the transaction time will be "now"...

If

where customer_id = '12345'

will return 1 million rows, then that will be slow to retrieve AND slow to sort. In the case above, a better index choice would probably be: customer_id, transaction_date

For example

SQL> drop table TXN purge;

Table dropped.

SQL> create table TXN as select * from dba_objects;

Table created.

SQL> create index IX on TXN ( owner, created ) ;

Index created.

SQL> set autotrace traceonly explain
SQL> select *
  2  from ( select * from TXN where owner = 'SYS' order by created desc )
  3  where rownum <= 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197688684

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    10 |  3680 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |      |       |       |            |          |
|   2 |   VIEW                         |      |    10 |  3680 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | TXN  |  2716 |   307K|     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX   |    10 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)
   4 - access("OWNER"='SYS')

I think its probably not strenuous an exercise for you to take the example I just posted, and use a timestamp instead of a date and see what happens.

Do a "describe" on TXN and you'll see what I mean.

It could be *anything*...

eg

select col1, col2
from my_table;

Lets say "my_table" has 300 columns. It *might* be benefical to index col1,col2 simply so the query above can scan through the whole index, which will be "thinner" (ie, smaller) than the whole table (with its 298 extra columns).

Indexes are all the same, ie, does the *benefit* in having it outweigh the *cost* of having

Hope this helps.