SQL Question: Are there pointers in SQL?

[Sureshot324]

Say my database has two tables, accounts and transactions. Every transaction is associated with a certain account, account_id being the foreign key.

Lets say an application which is integrated with this database wants to retrieve one account and all it's transactions. One way would be
SELECT * FROM accounts WHERE account_id=123
SELECT * FROM transactions WHERE account_id=123
This could be slow if the tables are large since it has to search through the entire transactions table to find all the transactions with that id. In a regular programming language however, this is fast, because the account object would have a pointer to an array of all it's transaction objects.

Is there any standard SQL way to implement such a pointer, so that once we find the account row, we have pointers to the corresponding rows in the transactions table?

 

[chusteczka]

There is not a pointer datatype in Oracle. Your question confuses three distinct aspects of databases:

• database programming,
• database design, and
• database querying.

An index is closest to your example of a sorted array. This index is most often created on the primary and foreign keys of a table and would most likely best suit your needs.

If you have the account number, you may create a view that merges the two tables together for your specific output needs. Else, you may search the ACCOUNTS table for a person's last name, social security number, or some other form of identification to obtain the ACCOUNT.account_id.

From there, you can use that result to return the results from the transactions table. An example:

create index accounts_idx on ACCOUNTS (account_id);
create index transactions_idx on TRANSACTIONS (account_id);

select A.account_id, A.name, A.ssn, T.*
from ACCOUNTS A, TRANSACTIONS T
where A.account_id = T.account_id
and A.ssn = '123456789';


The optimization you appear to seek is handled automatically within the query by the Oracle optimization engine.

 

[chusteczka]

Cursors
Thinking deeper on this subject, Cursors are considered to be a form of pointer to a specific row. Implicit Cursors are within the Oracle code that translates and performs the SQL queries behind the scenes. Explicit Cursors are explicitly created by the programmer to assist with table updates and inserts when the data is taken from another table.

TechOnTheNet - Cursors
O'Reilly - Oracle PL/SQL Programming - Explicit Cursors (search)


References - REF, DEREF
This may be what you are looking for. There is discussion of a stored reference to a row with a corresponding dereference. This is a new topic for me so I will just point you to the materials for you to learn it on your own.

Oracle9i JDBC Developer's Guide and Reference
O'Reilly - Oracle PL/SQL Programming - page 1003

 

[jvroig]

This could be slow if the tables are large since it has to search through the entire transactions table to find all the transactions with that id.

Assuming you have primary keys and/or any other indexes in that table, and it is one of those indexes that you use in your WHERE clause, then no, the entire table most certainly need not be searched through.

In your example, you would have some sort of "transaction_id" for your transaction table as primary key. If you are concerned about query speed whenever you do "SELECT * FROM transactions WHERE account_id=123", then you can index `account_id` (or "make the `account_id` field an index, however you wish to call the procedure).

 

[Sureshot324]

Thanks for the info. I can see I have some reading to do.