| Subject | Re: Index is corrupt |
|---|---|
| Author | svanderclock |
| Post date | 2010-02-27T08:46:37Z |
dear ann,
i add at the bottom of this message the stucture of one of these 40 tables. most of the time (on a new restored database) the index deph is 3 and on very few index it's even 4. so for this few index (only 11 in all the database that have 1907 index) did i need to increase the page size ?
also my mean probleme is now to speed up this insert ! actually i do them in a bulk procedure (so the 300 000 insert in one transaction). do you think it's can help if i split it in 300 transactions of 1000 bulk insert ?)
1/ the firebird.log
2/ the gfix result
3/ the client connection that "freeze" and can not connect anymore to the server.
however for the 3 it's "suspicious", because i also discover that just after restored the database and open it with lot of client waiting fb_server in queue, the fb_server "freeze" with so much simultaneous client ! no other choice to restart the server
exemple of one of the 40 heavy 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;
TRANSAC_SEARCH_RES_FS_A_2 (276)
Primary pointer page: 634, Index root page: 635
Data pages: 182861, data page slots: 182861, average fill: 82%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 928
80 - 99% = 181933
Index TRANSAC_SEARCH_100_IDX (2)
Depth: 3, leaf buckets: 5863, nodes: 5150351
Average data length: 0.01, total dup: 5146112, max dup: 2235573
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 5862
Index TRANSAC_SEARCH_101_IDX (3)
Depth: 3, leaf buckets: 5703, nodes: 5150351
Average data length: 0.00, total dup: 5150293, max dup: 2779803
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 5702
Index TRANSAC_SEARCH_102_IDX (4)
Depth: 3, leaf buckets: 6229, nodes: 5150351
Average data length: 0.00, total dup: 5150279, max dup: 1186843
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 6228
Index TRANSAC_SEARCH_103_IDX (0)
Depth: 3, leaf buckets: 6572, nodes: 5150351
Average data length: 0.10, total dup: 4741612, max dup: 29
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 6571
Index TRANSAC_SEARCH_104_IDX (5)
Depth: 3, leaf buckets: 10960, nodes: 5150351
Average data length: 2.82, total dup: 31952, max dup: 3
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 10959
Index TRANSAC_SEARCH_105_IDX (6)
Depth: 3, leaf buckets: 11388, nodes: 5150351
Average data length: 2.88, total dup: 554640, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 11387
Index TRANSAC_SEARCH_106_IDX (7)
Depth: 3, leaf buckets: 12365, nodes: 5150351
Average data length: 4.10, total dup: 3105504, max dup: 3330
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12364
Index TRANSAC_SEARCH_1071_IDX (19)
Depth: 3, leaf buckets: 11259, nodes: 5150351
Average data length: 3.01, total dup: 27337, max dup: 3
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11258
Index TRANSAC_SEARCH_1072_IDX (20)
Depth: 3, leaf buckets: 11607, nodes: 5150351
Average data length: 3.03, total dup: 554598, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 11606
Index TRANSAC_SEARCH_1073_IDX (21)
Depth: 4, leaf buckets: 12884, nodes: 5150351
Average data length: 4.45, total dup: 2989561, max dup: 1491
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12883
Index TRANSAC_SEARCH_1074_IDX (22)
Depth: 4, leaf buckets: 12879, nodes: 5150351
Average data length: 4.45, total dup: 2989561, max dup: 1491
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12878
Index TRANSAC_SEARCH_1075_IDX (23)
Depth: 3, leaf buckets: 9573, nodes: 5150351
Average data length: 2.17, total dup: 4236157, max dup: 209400
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 9572
Index TRANSAC_SEARCH_1076_IDX (24)
Depth: 3, leaf buckets: 7195, nodes: 5150351
Average data length: 0.52, total dup: 4816642, max dup: 209400
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 7194
Index TRANSAC_SEARCH_1077_IDX (25)
Depth: 3, leaf buckets: 7204, nodes: 5150351
Average data length: 0.52, total dup: 4801561, max dup: 64520
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 7203
Index TRANSAC_SEARCH_107_IDX (8)
Depth: 3, leaf buckets: 12359, nodes: 5150351
Average data length: 4.09, total dup: 3105504, max dup: 3330
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 12358
Index TRANSAC_SEARCH_1080_IDX (26)
Depth: 3, leaf buckets: 11910, nodes: 5150351
Average data length: 3.42, total dup: 18655, max dup: 3
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11909
Index TRANSAC_SEARCH_1081_IDX (27)
Depth: 4, leaf buckets: 12194, nodes: 5150351
Average data length: 3.44, total dup: 509528, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12194
Index TRANSAC_SEARCH_1082_IDX (28)
Depth: 4, leaf buckets: 13853, nodes: 5150351
Average data length: 5.12, total dup: 2761426, max dup: 1167
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 13853
Index TRANSAC_SEARCH_1083_IDX (29)
Depth: 4, leaf buckets: 13847, nodes: 5150351
Average data length: 5.12, total dup: 2761426, max dup: 1167
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 13846
Index TRANSAC_SEARCH_1084_IDX (30)
Depth: 4, leaf buckets: 10137, nodes: 5150351
Average data length: 2.56, total dup: 4146854, max dup: 147372
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 10136
Index TRANSAC_SEARCH_1085_IDX (31)
Depth: 3, leaf buckets: 7540, nodes: 5150351
Average data length: 0.75, total dup: 4756102, max dup: 147372
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7539
Index TRANSAC_SEARCH_1086_IDX (32)
Depth: 3, leaf buckets: 7550, nodes: 5150351
Average data length: 0.76, total dup: 4733407, max dup: 48592
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 7549
Index TRANSAC_SEARCH_108_IDX (9)
Depth: 3, leaf buckets: 9102, nodes: 5150351
Average data length: 1.85, total dup: 4339738, max dup: 216794
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 9101
Index TRANSAC_SEARCH_109_IDX (10)
Depth: 3, leaf buckets: 7005, nodes: 5150351
Average data length: 0.39, total dup: 4849093, max dup: 245406
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7004
Index TRANSAC_SEARCH_110_IDX (11)
Depth: 3, leaf buckets: 7013, nodes: 5150351
Average data length: 0.40, total dup: 4831182, max dup: 101128
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7013
Index TRANSAC_SEARCH_111_IDX (12)
Depth: 3, leaf buckets: 11578, nodes: 5150351
Average data length: 3.21, total dup: 21140, max dup: 3
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11577
Index TRANSAC_SEARCH_112_IDX (13)
Depth: 3, leaf buckets: 11928, nodes: 5150351
Average data length: 3.26, total dup: 509547, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11927
Index TRANSAC_SEARCH_113_IDX (14)
Depth: 4, leaf buckets: 13279, nodes: 5150351
Average data length: 4.73, total dup: 2879750, max dup: 1576
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 13278
Index TRANSAC_SEARCH_114_IDX (15)
Depth: 4, leaf buckets: 13274, nodes: 5150351
Average data length: 4.73, total dup: 2879750, max dup: 1576
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 13273
Index TRANSAC_SEARCH_115_IDX (16)
Depth: 3, leaf buckets: 9607, nodes: 5150351
Average data length: 2.20, total dup: 4255381, max dup: 149987
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 9606
Index TRANSAC_SEARCH_116_IDX (17)
Depth: 3, leaf buckets: 7305, nodes: 5150351
Average data length: 0.60, total dup: 4792091, max dup: 165768
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7305
Index TRANSAC_SEARCH_117_IDX (18)
Depth: 3, leaf buckets: 7316, nodes: 5150351
Average data length: 0.60, total dup: 4767251, max dup: 75952
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 7315
Index TRANSAC_SEARCH_99_IDX (1)
Depth: 3, leaf buckets: 6519, nodes: 5150351
Average data length: 0.07, total dup: 5103687, max dup: 37474
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 6518
thanks by advance
stephane
> Post hoc, ergo proctor hoc? OK, lets continue with that assumption...meus latin est non valde bonus :)
> > i split one table with 12 index in 40 tables with 2000 index (and add 10 timesi past at the end of this message the gtat result (from a new restored database) for one of these table. seam to be ok, except that the average length of the depht is 3 (and on 2 index it's even 4)
> > more row in it at the same time).
>
> What does gstat say about the indexes - depth, number of nodes,
> compression, etc.?
> > strangely few day after that we start toyes tons of read compare to the number of write ! but write are low but very slow to execute
> > have database corruption :( our database is very intensive (we have around
> > 500 fb_inet_server.exe open every time for example).
>
> I'm guessing that you've got tons of reads and almost no writes.
> > also adding or deleting row to these 40 databases is a very low process. dounfortunatly i mean very slow. for exemple it's now 2 days i start a query to add 300 000 rows in these tables... query is still running :(
> > you think i can pass the pagesize from 4ko to 8ko can help ?
>
> Adding and deleting are "low" meaning there isn't much, or "slow"?
> That depends on what gstat shows. As long as the index depth is
> not more than 3, then increasing the page size won't help much.
i add at the bottom of this message the stucture of one of these 40 tables. most of the time (on a new restored database) the index deph is 3 and on very few index it's even 4. so for this few index (only 11 in all the database that have 1907 index) did i need to increase the page size ?
also my mean probleme is now to speed up this insert ! actually i do them in a bulk procedure (so the 300 000 insert in one transaction). do you think it's can help if i split it in 300 transactions of 1000 bulk insert ?)
> > also do you thing that the superserver could be better than the classichmm, gfix don't say me where the bad node was, simply say me 69 index error found
> > server on such bug? or i can wait firebird 2.5 (when it's will be
> available) ?
> >
> Are your bad nodes all level 1? Are any level 0?
> Do you have any evidence, other than the gfix validation, that the indexyes, i have
> is not working correctly? Are you getting the expected results? Gfix
> validation is sometimes fussy about things that really aren't problems.
1/ the firebird.log
2/ the gfix result
3/ the client connection that "freeze" and can not connect anymore to the server.
however for the 3 it's "suspicious", because i also discover that just after restored the database and open it with lot of client waiting fb_server in queue, the fb_server "freeze" with so much simultaneous client ! no other choice to restart the server
exemple of one of the 40 heavy 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;
TRANSAC_SEARCH_RES_FS_A_2 (276)
Primary pointer page: 634, Index root page: 635
Data pages: 182861, data page slots: 182861, average fill: 82%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 928
80 - 99% = 181933
Index TRANSAC_SEARCH_100_IDX (2)
Depth: 3, leaf buckets: 5863, nodes: 5150351
Average data length: 0.01, total dup: 5146112, max dup: 2235573
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 5862
Index TRANSAC_SEARCH_101_IDX (3)
Depth: 3, leaf buckets: 5703, nodes: 5150351
Average data length: 0.00, total dup: 5150293, max dup: 2779803
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 5702
Index TRANSAC_SEARCH_102_IDX (4)
Depth: 3, leaf buckets: 6229, nodes: 5150351
Average data length: 0.00, total dup: 5150279, max dup: 1186843
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 6228
Index TRANSAC_SEARCH_103_IDX (0)
Depth: 3, leaf buckets: 6572, nodes: 5150351
Average data length: 0.10, total dup: 4741612, max dup: 29
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 6571
Index TRANSAC_SEARCH_104_IDX (5)
Depth: 3, leaf buckets: 10960, nodes: 5150351
Average data length: 2.82, total dup: 31952, max dup: 3
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 10959
Index TRANSAC_SEARCH_105_IDX (6)
Depth: 3, leaf buckets: 11388, nodes: 5150351
Average data length: 2.88, total dup: 554640, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 11387
Index TRANSAC_SEARCH_106_IDX (7)
Depth: 3, leaf buckets: 12365, nodes: 5150351
Average data length: 4.10, total dup: 3105504, max dup: 3330
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12364
Index TRANSAC_SEARCH_1071_IDX (19)
Depth: 3, leaf buckets: 11259, nodes: 5150351
Average data length: 3.01, total dup: 27337, max dup: 3
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11258
Index TRANSAC_SEARCH_1072_IDX (20)
Depth: 3, leaf buckets: 11607, nodes: 5150351
Average data length: 3.03, total dup: 554598, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 11606
Index TRANSAC_SEARCH_1073_IDX (21)
Depth: 4, leaf buckets: 12884, nodes: 5150351
Average data length: 4.45, total dup: 2989561, max dup: 1491
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12883
Index TRANSAC_SEARCH_1074_IDX (22)
Depth: 4, leaf buckets: 12879, nodes: 5150351
Average data length: 4.45, total dup: 2989561, max dup: 1491
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12878
Index TRANSAC_SEARCH_1075_IDX (23)
Depth: 3, leaf buckets: 9573, nodes: 5150351
Average data length: 2.17, total dup: 4236157, max dup: 209400
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 9572
Index TRANSAC_SEARCH_1076_IDX (24)
Depth: 3, leaf buckets: 7195, nodes: 5150351
Average data length: 0.52, total dup: 4816642, max dup: 209400
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 7194
Index TRANSAC_SEARCH_1077_IDX (25)
Depth: 3, leaf buckets: 7204, nodes: 5150351
Average data length: 0.52, total dup: 4801561, max dup: 64520
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 7203
Index TRANSAC_SEARCH_107_IDX (8)
Depth: 3, leaf buckets: 12359, nodes: 5150351
Average data length: 4.09, total dup: 3105504, max dup: 3330
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 12358
Index TRANSAC_SEARCH_1080_IDX (26)
Depth: 3, leaf buckets: 11910, nodes: 5150351
Average data length: 3.42, total dup: 18655, max dup: 3
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11909
Index TRANSAC_SEARCH_1081_IDX (27)
Depth: 4, leaf buckets: 12194, nodes: 5150351
Average data length: 3.44, total dup: 509528, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12194
Index TRANSAC_SEARCH_1082_IDX (28)
Depth: 4, leaf buckets: 13853, nodes: 5150351
Average data length: 5.12, total dup: 2761426, max dup: 1167
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 13853
Index TRANSAC_SEARCH_1083_IDX (29)
Depth: 4, leaf buckets: 13847, nodes: 5150351
Average data length: 5.12, total dup: 2761426, max dup: 1167
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 13846
Index TRANSAC_SEARCH_1084_IDX (30)
Depth: 4, leaf buckets: 10137, nodes: 5150351
Average data length: 2.56, total dup: 4146854, max dup: 147372
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 10136
Index TRANSAC_SEARCH_1085_IDX (31)
Depth: 3, leaf buckets: 7540, nodes: 5150351
Average data length: 0.75, total dup: 4756102, max dup: 147372
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7539
Index TRANSAC_SEARCH_1086_IDX (32)
Depth: 3, leaf buckets: 7550, nodes: 5150351
Average data length: 0.76, total dup: 4733407, max dup: 48592
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 7549
Index TRANSAC_SEARCH_108_IDX (9)
Depth: 3, leaf buckets: 9102, nodes: 5150351
Average data length: 1.85, total dup: 4339738, max dup: 216794
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 9101
Index TRANSAC_SEARCH_109_IDX (10)
Depth: 3, leaf buckets: 7005, nodes: 5150351
Average data length: 0.39, total dup: 4849093, max dup: 245406
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7004
Index TRANSAC_SEARCH_110_IDX (11)
Depth: 3, leaf buckets: 7013, nodes: 5150351
Average data length: 0.40, total dup: 4831182, max dup: 101128
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7013
Index TRANSAC_SEARCH_111_IDX (12)
Depth: 3, leaf buckets: 11578, nodes: 5150351
Average data length: 3.21, total dup: 21140, max dup: 3
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11577
Index TRANSAC_SEARCH_112_IDX (13)
Depth: 3, leaf buckets: 11928, nodes: 5150351
Average data length: 3.26, total dup: 509547, max dup: 139
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 11927
Index TRANSAC_SEARCH_113_IDX (14)
Depth: 4, leaf buckets: 13279, nodes: 5150351
Average data length: 4.73, total dup: 2879750, max dup: 1576
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 13278
Index TRANSAC_SEARCH_114_IDX (15)
Depth: 4, leaf buckets: 13274, nodes: 5150351
Average data length: 4.73, total dup: 2879750, max dup: 1576
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 13273
Index TRANSAC_SEARCH_115_IDX (16)
Depth: 3, leaf buckets: 9607, nodes: 5150351
Average data length: 2.20, total dup: 4255381, max dup: 149987
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 9606
Index TRANSAC_SEARCH_116_IDX (17)
Depth: 3, leaf buckets: 7305, nodes: 5150351
Average data length: 0.60, total dup: 4792091, max dup: 165768
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 7305
Index TRANSAC_SEARCH_117_IDX (18)
Depth: 3, leaf buckets: 7316, nodes: 5150351
Average data length: 0.60, total dup: 4767251, max dup: 75952
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 7315
Index TRANSAC_SEARCH_99_IDX (1)
Depth: 3, leaf buckets: 6519, nodes: 5150351
Average data length: 0.07, total dup: 5103687, max dup: 37474
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 6518
thanks by advance
stephane