Subscribe to this thread
Home - General / All posts - M9 automatically transfered indexes through queries
apo
171 post(s)
#01-Apr-19 21:23

Just a little question.

I recently faced a "cannot add record" using a join query. Trying to understand why, I found that the query engine ,creating new table as result (using a INTO), is also copying the indexes. The operated join was duplicating the mfd_id (btree by nature) and failed injecting more than one line.

There are situations were this "automatic" transfer of the indexes is annoying. My current trick is to multiply by 1 or any other identity operator to avoid the transfer of indexes but I would find smart to be able to "switch on/off" this type of "de facto" transfer. No idea if this is possible.

The test is in the .map in annex.

Attachments:
test.map

tjhb
10,094 post(s)
#02-Apr-19 08:27

It is not a question of copying the indexes, but of mfd_id being special.

If you insert a list of mfd_id values into a table, whether new or existing, with that field name, then Manifold will automatically attempt to build or manage a BTREE index on the field.

As soon as a duplicate value arises, the insert must fail (since mfd_id must always be unique).

So in general, avoid inserting mfd_id vales into a new or existing table.

You can do it, if you are certain that the values are (and will remain) unique. Then it is handled perfectly. But usually there is no need to.

Instead, usually, if you need source mfd_id values in the target, rename them into a field with a different name (without a BTREE index). Such as source_id.

apo
171 post(s)
#02-Apr-19 11:28

First of all I normally prefer to be certain of the structure of my data in and out of my sql but it doesn't mean I succeed in that. My problem was here illustrated with the mfd_id but is exactly the same with any other btree unique and non null index. The example of a land plot layer with unique ids indexed that I split by a pollution zones layer and want to retrieve both the plot id and the poll. zone id.

But I will stick on my trick or yours (renaming approach).

tjhb
10,094 post(s)
#02-Apr-19 08:45

But what if you need a mfd_id field in your target table? Good question.

For an existing table, mfd_id is populated with new unique values automatically.

If your query is an INSERT INTO, to a table that already has a mfd_id field, then that is the same thing. Just leave mfd_id out, and Manifold will supply new unique values automatically.

If your query is SELECT INTO, fill it first, then add a mfd_id field and its BTREE index after inserting data. Again it will be filled automatically.

apo
171 post(s)
#02-Apr-19 11:30

Yes I intensively use the alter table to

- add the mfd_id and related index

- put the tables in the right folder (to be a bit less messy in my projects than on my desk)

adamw


10,447 post(s)
#02-Apr-19 17:18

We copy indexes as part of SELECT INTO because that feels natural for our data model where indexes belong to the table and not to the daabase. We do not currently have any means to turn that off, but we might add them - either a TableCacheXxx variant that would strip indexes or maybe a separate keyword just for SELECT INTO (SELECT ... INTO ... NOINDEXES FROM ...) or both. Is this limited to SELECT INTO?

PS: Maybe we should reverse the behavior and have the default for SELECT INTO to be just insert fields, because that's similar to SQL Server / Oracle and others, and use an extra keyword or keywords to copy indexes (SELECT ... INTO ... WITH INDEXES FROM ...).

apo
171 post(s)
#02-Apr-19 21:51

It is amazing how further M9 is going compare to others DB I use, love it.

My preference is turned toward your second solution for the similarity with the others but in the meantime both are easy to handle.

To answer your first question, I didn't faced another situation than the SELECT INTO so far

Many thanks for the time you spend and the interest you show for my little questions

tjhb
10,094 post(s)
#03-Apr-19 01:31

Sorry for having assumed that you meant specifically mfd_id.

I should have tested with non-special fields and their indexes as well. I learnt something useful.

Thanks both of you.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.