Hello Sander,

> below the table :
>
> CREATE TABLE TRANSAC_SEARCH_RES_FS_A_2
> (
> ID_geo_place VARCHAR(100),
> ID_holder_agency VARCHAR(15) NOT NULL,
> ID_reader VARCHAR(15),
> Property_category SMALLINT NOT NULL,
> Advertisement_weight INTEGER NOT NULL,
> Creation_date TIMESTAMP NOT NULL,
> Total_sale_price NUMERIC(15,5),
> Surface NUMERIC(12,4),
> Nb_room SMALLINT,
> Nb_bedroom SMALLINT,
> ID_property VARCHAR(15) NOT NULL,
> ID_transac VARCHAR(15) NOT NULL
> );
>
> commit;
>
>
> /** single column **/
> CREATE ASCENDING INDEX TRANSAC_SEARCH_103_IDX ON TRANSAC_SEARCH_RES_FS_A_2
> (ID_transac);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_99_IDX ON TRANSAC_SEARCH_RES_FS_A_2
> (Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_100_IDX ON TRANSAC_SEARCH_RES_FS_A_2
> (Surface);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_101_IDX ON TRANSAC_SEARCH_RES_FS_A_2
> (NB_room);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_102_IDX ON TRANSAC_SEARCH_RES_FS_A_2
> (NB_bedroom);
>
> /** ID_geo_place, ID_reader, Transac_kind **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_104_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_105_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_106_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_107_IDX ON TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_108_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_109_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_110_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, NB_bedroom);
>
> /** ID_geo_place, ID_reader, Transac_kind, Property_category **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_111_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Property_category, Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_112_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Property_category, Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_113_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Property_category, Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_114_IDX ON TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Property_category, Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_115_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Property_category, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_116_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Property_category, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_117_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_reader, Property_category, NB_bedroom);
>
> /** ID_geo_place, ID_holder_agency, ID_reader, Transac_kind **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1071_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1072_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1073_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_1074_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1075_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1076_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1077_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, NB_bedroom);
>
> /** ID_geo_place, ID_holder_agency, ID_reader, Transac_kind,
> Property_category
> **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1080_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Property_category,
> Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1081_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Property_category,
> Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1082_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Property_category,
> Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_1083_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Property_category,
> Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1084_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Property_category, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1085_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Property_category, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1086_IDX ON
> TRANSAC_SEARCH_RES_FS_A_2
> (ID_geo_place, ID_holder_agency, ID_reader, Property_category,
> NB_bedroom);
>
> commit;
>
>
>
>
> with all these index, i can handle all the query like
>
> select ... where
> ID_geo_place=xxx
> and ID_reader = yyy
> and Property_category = www
> order by
> Creation_date desc
>
> here for exemple i will simply specify in the query plan to use the
>
> INDEX TRANSAC_SEARCH_112_IDX (ID_geo_place, ID_reader, Property_category,
> Creation_date);
>
> and replace the "order by" by :
> ID_geo_place desc,
> ID_reader desc,
> Property_category desc,
> Creation_date desc
>
> doesn't matter if (for exemple) ID_geo_place=xxx will return millions of
> row or just one, the result will be alway < 100 ms. with only my few set
> of index i can handle most of all the situation
>

So, the resultset itself can be large, but you only need a few rows? Do you
ever need
such large resultsets?

When an index is used, the engine returns the first few rows quickly, but
pulling the
entire resultset will probably take more time than without an index.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com