Title: Querying and sorting a big table in Oracle 12c
Tags: sql;oracle;oracle12c
Question: Let's say I have a personal data table Table1: first_name, last_name, date_of_birth, address. Millions of rows. What indexes should I create to be able to run fast following queries:
select * from Table1 where date_of_birth &gt; some_date and contains(address,'some street') &gt; 0  order by first_name offset 100 rows fetch next 5 rows only;
select * from Table1 where date_of_birth &gt; some_date and contains(address,'some street') &gt; 0 order by last_name offset 100 rows fetch next 5 rows;
That's a simplified case, but what I am trying to figure out is how to build a DB structure for a website similar to ebay, i.e. several sorting orders, several numeric parameters and text description. I've got text indexes on address already
Comment: but could you comment for the case that I described? Just one table and two sorting orders. That's quite specific

Comment: @Toolkit Execute your query and provide the explain plan here so that others would be able to help you in a better way. In order to get the explain plan of your query, please do a google search and will find plenty of resources.

Comment: This is way too broad a question for StackOverflow. Apart from anything else, how eBay like do you want to go ?

Comment: It would be better if you add some things you tried and how the queries behave(duration of execution)


Here is another answer: It depends on various conditions if an index is useful or not.
Distribution of your data (e.g. range of date_of_birth in your table)
Selection parameters (e.g. selection with date_of_birth &gt; 1900-01-01)
Expected result set of your selection (order by)
Table size
Your database environment
In some cases a full table scan is the better option.
To decide this you have to study the data of your table, examine your business cases, investigate the execution plan
In some cases you can improve your select statement.
restrict range for date_of_birth with an upper limit.
where date_of_birth between some_date and some_date +7
this is not identical to your select statement (order by name only in this range) but maybe good enough and significantly improves performance
Comment for this answer: i don't think DB environment has to do with the question. DB size is mentioned in the post - several million records. Date of birth range also doesn't have much to do. Have you done yourself similar tasks? How do you solve the sorting (or pre-sorting) of a big set? Let's say you have 10 million records, you search query brings you 5000, you want to get first 5. Currently I am using presorted mviews, but what other options. Please be specific
Comment for this answer: OPTIMIZER_INDEX_COST_ADJ is default 100%, date_of_birth is uniformly distributed. Any solution?
Comment for this answer: Sorry, but the answer is as specific as your question. DB environment settings has to do with this question (e.g. OPTIMIZER_INDEX_COST_ADJ). And if your table contains date_of_birth in the range of 1920-01-01 upto 2017-01-01 (uniformly destributed) and you select with `date_of_birth > 01-01-1930' then an index on date_of_birth will be useless.
Comment for this answer: OPTIMIZER_INDEX_COST_ADJ and distribution of date_of_birth are only two examples of many many conditions. Please post the execution plan in your question to get further help. Furthermore the range of date_of_birth is important.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:05/05/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><oracle><oracle12c>', 'Title': 'Querying and sorting a big table in Oracle 12c', 'ViewCount': 192, 'id': 43797774, 'user_AboutMe': '', 'user_AccountId': 315712.0, 'user_DisplayName': 'Toolkit', 'user_Reputation': 10319.0, 'user_Views': 1860.0, 'user_id': 631527.0}}