sorting or temporary tables in execution plan?

#21

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

#22

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

#23

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

#24

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

#25

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

#26

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

#27

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

#28

Darren <anon5874 (AT) yahoo (DOT) com> writes:

Quote:

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim

--
tcross (at) rapttech dot com dot au

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

sorting or temporary tables in execution plan?

comp.databases.theory comp.databases.theory

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM

Re: sorting or temporary tables in execution plan? - 08-21-2008 , 11:13 PM