Sort optimization
A system and method for processing of queries including receiving a query including a set operation and a sort operation, wherein the set operation includes a first data structure and a second data structure and the sort operation requests a result set that is sorted based on a column or attribute of the first data structure and a column or attribute of the second data structure; generating a query plan in which a sort operation occurs prior to the set operation; determining a first, partial set of one or more resultant rows responsive to the query; sending the first, partial set of one or more resultant rows responsive to the query to a client; determining a second, partial set of one or more resultant rows responsive to the query; and sending the second, partial set of one or more resultant rows to the client.
1. A computer-implemented method comprising:
receiving, using one or more processors, a query including a set operation and a sort operation, wherein the set operation includes a first data structure and a second data structure and the sort operation requests a result set that is sorted based on a column or attribute of the first data structure and a column or attribute of the second data structure;
generating, using the one or more processors, a query plan in which a sort operation occurs prior to the set operation;
determining, using the one or more processors, a first, partial set of one or more resultant rows responsive to the query;
sending, using the one or more processors, the first, partial set of one or more resultant rows responsive to the query to a client;
determining, using the one or more processors, a second, partial set of one or more resultant rows responsive to the query; and
sending, using the one or more processors, the second, partial set of one or more resultant rows responsive to the query to the client.
2. The computer-implemented method of claim 1 , wherein determining the first, partial set of one or more resultant rows responsive to the query includes:
buffering a first portion of the first, partial set of one or more resultant rows; and
merging a second portion of the first, partial set of one or more resultant rows to the first portion of the first, partial set of one or more resultant rows in the buffer,
wherein the first portion and second portion of the first, partial set of one or more resultant rows have a common value in the column or attribute of the first data structure on which the sort operation is requested.
3. The computer-implemented method of claim 2 including:
reading a row from the first data structure
determining the row from the first data structure has a different value in the column or attribute of the first data structure on which the sort operation is requested;
responsive to determining the row from the first data structure has different value in the column or attribute of the first data structure and after merging the second portion of the first, partial set of one or more resultant rows to the first portion of the first, partial set of one or more resultant rows in the buffer, sending the first portion and second portion of the first, partial set of one or more resultant rows, as the first, partial set of one or more resultant rows; and
clearing the buffer.
4. The computer-implemented method of claim 1 including:
determining, using the one or more processors, whether the column or attribute of the first data structure has an index of the first data structure defined on it; and
determining, using the one or more processors, whether the column or attribute of the second data structure has an index of the second data structure defined on it.
5. The computer-implemented method of claim 1 , wherein the first data structure and the second data structure are relational tables.
6. The computer-implemented method of claim 1 , wherein the first data structure and the second data structure are non-relational data structures.
7. The computer-implemented method of claim 1 , wherein a full result set comprises the first, partial set of one or more resultant rows responsive to the query, and the second, partial set of one or more resultant rows responsive to the query.
8. The computer-implemented method of claim 1 , wherein the query uses structured query language (SQL).
9. The computer-implemented method of claim 1 , wherein the set operation is one or more of an inner join, left outer join, right outer join, full outer join and cross join.
10. A system comprising:
one or more processors; and
a memory storing instructions that, when executed by the one or more processors, cause the system to:
receive a query including a set operation and a sort operation, wherein the set operation includes a first data structure and a second data structure and the sort operation requests a result set that is sorted based on a column or attribute of the first data structure and a column or attribute of the second data structure;
generate a query plan in which a sort operation occurs prior to the set operation;
determine a first, partial set of one or more resultant rows responsive to the query;
send the first, partial set of one or more resultant rows responsive to the query to a client;
determine a second, partial set of one or more resultant rows responsive to the query; and
send the second, partial set of one or more resultant rows responsive to the query to the client.
11. The system of claim 10 , wherein determining the first, partial set of one or more resultant rows responsive to the query includes:
buffering a first portion of the first, partial set of one or more resultant rows; and
merging a second portion of the first, partial set of one or more resultant rows to the first portion of the first, partial set of one or more resultant rows in the buffer,
wherein the first portion and second portion of the first, partial set of one or more resultant rows have a common value in the column or attribute of the first data structure on which the sort operation is requested.
12. The system of claim 11 , wherein the instructions, when executed by the one or more processors, cause the system to:
read a row from the first data structure
determine the row from the first data structure has a different value in the column or attribute of the first data structure on which the sort operation is requested;
responsive to determining the row from the first data structure has different value in the column or attribute of the first data structure and after merging the second portion of the first, partial set of one or more resultant rows to the first portion of the first, partial set of one or more resultant rows in the buffer, send the first portion and second portion of the first, partial set of one or more resultant rows, as the first, partial set of one or more resultant rows; and
clear the buffer.
13. The system of claim 10 , wherein the instructions, when executed by the one or more processors, cause the system to:
determine whether the column or attribute of the first data structure has an index of the first data structure defined on it; and
determine whether the column or attribute of the second data structure has an index of the second data structure defined on it.
14. The system of claim 10 , wherein the first data structure and the second data structure are relational tables.
15. The system of claim 10 , wherein the first data structure and the second data structure are non-relational data structures.
16. The system of claim 10 , wherein a full result set comprises the first, partial set of one or more resultant rows responsive to the query, and the second, partial set of one or more resultant rows responsive to the query.
17. The system of claim 10 , wherein the query uses structured query language (SQL).
18. The system of claim 10 , wherein the set operation is one or more of an inner join, left outer join, right outer join, full outer join and cross join.