Euke Castellano wrote:
>
> Marcus Obwandner wrote:
>
> >
> >
> > Euke Castellano schrieb:
> >
> >> Marcus Obwandner wrote:
> >>
> >> ...
> >
> >
> >>>
> >> Thanks Marcus:
> >> This is my first experience with MaxDB and I think i don't catch some
> >> concept, because what happends, I think isn't normal. Perhaps
> >> something is wrong configured, i don't know ....
> >
> > For me that is not a MAxDB Problem more an SQL Design Problem.
> >
> >> 1) Really I don't know which is the column/s that the final user is
> >> going to choose, so i can't create 10 or more indexes on a 25000000
> >> rows table, �isn't it?. The only required condition is on invoiceDate
> >> column, that's the reason of IDX_CONS_DATE.
> >
> > This is clear but after a little look over the Table structure I would
> > set following Indexes:
> >
> > hotel, service, segment # these are references I think
> > invoicedate, rate, processdate # these are Values which might be
> > usefull for the controling.
> >
> > But if you think this cost you to much resources (well time is also a
> > resource, if a SQL Statement is such expensive that it's running for
> > hours)
> >
> > I don't know anything about your Project and Application so I don't
> > knew your, accordingly your costumer needs, so I could go totally
> > wrong with my thinking.
> >
> > But what a Solution could be is this:
> >
> > --- untested SQL starts here ----
> > CREATE TEMPORARY TABLE "consum_tmp"(
> > "id" Integer NOT NULL,
> > "hotel" Integer NOT NULL,
> > "service" Integer NOT NULL,
> > "segment" Integer NOT NULL,
> > "invoicedate" Date,
> > "rate" Integer,
> > PRIMARY KEY ("id"),
> > KEY rate,
> > KEY invoicedate
> > )
> >
> >
> > # make some out of many
> > INSERT INTO consum_tmp (id,hotel,service,segment,rate,invoiceDate)
> > SELECT
> > id,hotel,service,segment,rate,invoiceDate
> > FROM
> > CONSUM
> > WHERE
> > invoiceDate BETWEEN '2004-01-01' AND '2004-01-31';
> >
> > # get the needed rate
> > SELECT
> > id,hotel,service,segment,rate,invoiceDate
> > FROM
> > consum_tmp
> > WHERE
> > rate=9;
> >
> > --- untested SQL ends here ----
> >
> > These queries create first a tmp Table and copy everything what is
> > needed and fast into the tmp table and create the indexes (it will be
> > faster to create the indexes after the insertion of the data, normally
> > about 10 up 20%)
> >
> > and then select the rating you need.
> >
> >> ...
> >> If someone needs more information, please ask me.
> >>
> >> Sorry for my english again and thank you very much.
> >>
> > I hope this help
> >
> > regards
> > Marcus Obwandner
>
>
>
> Thanks again Marcus:
>
>
> It seems MaxDB is not able to create index on temporary table. (in
> CREATE INDEX help content --> The specified table must be an existing
> base table, and not a temporary table).
>
>
Hi,
Did you listen to the answer Holger Becker gave 28. January?!
If you use the index for invoice_date (nearly 1 million entries out of a 25
million-entry-table), then during a select for which EXPLAIN tells you 'RESULT
IS NOT COPIED' the first index-entry is picked, checked if the primary table
fulfills the other conditions (none in this case) and finishes.
It knows that at least one row can be returned (no error 100 'row not found'
needed) and that is all what has to be done. This will not last very long.
But if you have another condition, one row after the other (using the index,
checking the corresponding primary data) has to be checked until the first row
is found fulfilling the other condition, too. And if maaaaaany rows will not
fulfill your rate=9-condition before the first one fulfilling it can be found,
then it will laaaaast a loooong time (depending on datacache-size, if your data
is IN your data cache, I/O speed and so on.
You only can avoid this by creating an index for rate or create an index for
the combination of rate and invoice_date.
Or you can compare the time with the first select until the first rate=9-row
will be returned to the time needed for the second select.
Elke
SAP Labs Berlin
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
