Database string / record pointers
I don't know the right words for what I am trying to figure out, and as such is not easy to search.
I want to know if it is possible with databases (regardless of technology, but it would be interesting to know if it is possible with Oracle, MySQL and Postgres) to point to specific rows instead of re-executing my query.
So I could run the query first, finding a few rows of interest, and then try to avoid re-searching them by specifying a list of pointers or some other metadata that points to a location in the database that I can jump to immediately. I want to get these results.
I understand that there is caching in the databases, but I want to keep those "pointers" still where and, as such caching does not ultimately solve the problem. Is it just an index and I store the index and look at it? most of my current tables don't have indexes and I don't want the speed drop that sometimes comes with indexes.
So what is the magic term that I am trying to enter into Google?
Greetings
source to share
In Oracle, it is called ROWID. It identifies the file, block number, and line number in that block. I can't say that what you are describing is a good idea, but it might at least make you start looking in the right direction.
More details here: http://www.orafaq.com/wiki/ROWID .
By the way, the "speed drop that comes with indexes" that you fear only matters if you are doing more attachments and updates than reading. Indexes only make reads faster, so if the read rate is high you may not have a problem and the index may be your best solution.
source to share
most of my current tables do not have indexes and I do not want the decreasing rate that sometimes comes with indexes.
And you also don't want the speed gains that usually come with indexes, but do you want to manually cast a pseudo cache instead?
I'm not here in vain, this is a serious moment. Database developers have spent a lot of effort and energy optimizing their products. Wouldn't it be wiser to learn how to leverage their efforts instead of re-implementing some of the core functionality?
source to share
In general, the best way to deal with this requirement is to use the primary key (or actually any convenient, compact unique identifier) as a "pointer" and rely on indexed searches to be fast - which it usually will.
You can use ROWID on more DBMSs than just Oracle, but this is generally not recommended for variety or reasons. If you fall for "every table that has an autoincrement design school" then you can write the autoincrement column values as identifiers.
You must have at least one index on (almost) all of your tables - that index will be on the primary key. The exception might be for a table so small that it fits easily into memory and won't update and will be used enough not to go out of memory. Then the index can be a distraction; however, such tables tend to be rarely updated, so the index won't hurt anything, and the optimizer will ignore it if the index doesn't (or may not) help.
You can have sub indexes as well. On a system where most of the activity is reading data, you may want to erro on the side of more indexes, not less, because access times are most critical. If your system has been heavily updated, you would specify fewer indexes because there is a cost associated with updating the indexes when updating, adding, deleting, or updating data. Understandably, you need to design indexes to work well with the queries that your users are actually running (or your applications are running).
source to share